﻿#coding: utf-8
__author__ = 'Administrator'
#jk409 update 2015-5-23
import pymysql
#import pymongo
#import sqlite3
import redis

#=================================================================
class Mysql:
    def __init__(self,host,user,password,db,port):
        self.host = host
        self.user = user
        self.passwd = password
        self.db = db
        self.port = int(port)
        self.charset = 'utf8'

    def read(self,sql,args=()):
        try:
            cnn = pymysql.connect(host=self.host,port=self.port,user=self.user, passwd=self.passwd, db=self.db,charset=self.charset)
            #self.cnn.autocommit(True)
            cur= cnn.cursor()
        except:
            print('数据库连接失败......')
            return False

        try:
            cur.execute(sql,args)
            data = cur.fetchall()
            #字典json格式的
            #data_dict = [dict((self.cur.description[i][0], value) for i, value in enumerate(row)) for row in self.cur.fetchall()]
            data = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in data]
            #print('\n',data)
        except:
            print('数据查询失败！')
            return False
        finally:
            cur.close()
            cnn.close()
        return data


    def write(self,sql,args=()):
        try:
            cnn = pymysql.connect(host=self.host,port=self.port,user=self.user, passwd=self.passwd, db=self.db, charset=self.charset)
            #self.cnn.autocommit(True)
            cur= cnn.cursor()
        except:
            print('数据库连接失败......')
            return False

        try:
            cur.execute(sql,args)
            cnn.commit()
        except:
            cnn.rollback()
            print('数据写入失败！')
            return False
        finally:
            cur.close()
            cnn.close()
        return True

#=================================================================
class Mongodb():
    def __init__(self, ip, port, db):
        self.conn = pymongo.Connection(ip, port)
        self.db = self.conn[db]

    def find(self,table):
        return self.db[table].find({}).limit(1500)

    def find_one(self,table,xarg):
        return self.db[table].find_one(xarg)

    def insert(self,table, xarg):
        try:
            data = self.db[table]
            data.insert(xarg)
        finally:
            self.conn.close()

    def remove(self,table ,xarg):
        try:
            data = self.db[table]
            data.remove(xarg)
        finally:
            self.conn.close()

    def save(self,table, xarg):
        try:
            data = self.db[table]
            data.save(xarg)
        finally:
            self.conn.close()

    def update(self,table ,*xarg):
        try:
            data = self.db[table]
            data.update(*xarg)
        finally:
            self.conn.close()

#=================================================================
class Redis():
    def __init__(self,host,port,db,passwd):
        #select 2#选择数据库2
        #self.cnn=redis.Connection(host='localhost',port=6379,db=0,password='',encoding='utf-8')
        #self.cnn=redis.Redis(host=host,port=port,db=0 ,password='',encoding='utf-8')
        self.host=host
        self.port=port
        self.db=db     #default value is 0 and select  1,2,3 and so on
        self.passwd=''
        self.encoding='utf-8'
        self.cnn = redis.Redis(host=self.host, port=self.port, db=self.db, password=self.passwd, encoding=self.encoding)

    def set(self, key,value):
        try:
            cnn = redis.Redis(host=self.host, port=self.port, db=self.db, password=self.passwd, encoding=self.encoding)
        except:
            print('连接Redis失败!')
            return False

        try:
            ret =cnn.set(key,value)
        except:
            print('Set操作失败')
            return False
        return ret

    def get(self,key):
        try:
            cnn = redis.Redis(host=self.host, port=self.port, db=self.db, password=self.passwd, encoding=self.encoding)
        except:
            print('连接Redis失败!')
            return False
        try:
            ret =cnn.get(key)
        except:
            print('Get操作失败!')
            return False
        return ret

    def getset(self, key,value):
        try:
            cnn = redis.Redis(host=self.host, port=self.port, db=self.db, password=self.passwd, encoding=self.encoding)
        except:
            print('连接Redis失败!')
            return False

        try:
            ret=cnn.getset(key,value)
        except:
            print('Getset操作失败!')
            return False
        return ret

    def append(self,key,value):
        try:
            cnn = redis.Redis(host=self.host, port=self.port, db=self.db, password=self.passwd, encoding=self.encoding)
        except:
            print('连接Redis失败!')
            return False
        try:
            ret =cnn.append(key, value)
        except:
            print('Append操作失败!')
            return False
        return ret

#=================================================================
class Sqlite3():
    def __init__(self,db):
        self.db=db

    def read(self,sql):
        try:
            cnn = sqlite3.connect(database=self.db)
            cur = cnn.cursor()
        except:
            print('连接[%s]失败'%self.db)
            return -1

        try:
            res = cur.execute(args)
        except:
            cnn.close()
            print('读取失败！')
            return False
        cnn.close()
        return  res

    def write(self, sql):
        try:
            cnn = sqlite3.connect(database=self.db)
            cur = cnn.cursor()
        except:
            print('连接[%s]失败' % self.db)
            return False

        try:
            res =cur.execute(args)
            cnn.commit()
        except:
            cnn.close()
            print('写入失败！')
            return False
        cnn.close()
        return res

    def INIT(self):
        sql = '''create table blog(
            id int,
            name varchar(32),
            email varchar(32),
            title varchar(32),
            fl varchar(32),
            tag varchar(32),
            date varchar(32),
            content varchar
        ) '''
        #sql2='alter table blog add column content  varchar;'
        self.cmd(sql)


if __name__ == '__main__':
    #s不太安全的sql写法
    #sqls='''select * from user where name like '%s' order by %s limit %s;'''
    #orders = 'id desc'
    #limits="0,2"
    #strs='test'+"%"
    #sqls2=sqls%(strs,orders,limits)
    #print sqls2
    #mysql=Mysql('127.0.0.1','root','123456','kkk').read(sqls%(strs,orders,limits))
    #print mysql
    #--------------------------------------------------------------------------------
    # 第2种方式
    #sqls = '''select * from user where name like %s  order by %s desc limit %s,%s;'''
    #orders = "id desc"
    #limits = "0,2"
    #wheres="name like 'test%' "
    #strs = 'test' + "%"
    #sqls2 = sqls % (strs, orders, limits)
    #print sqls2
    #mysql = Mysql('127.0.0.1', 'root', '123456', 'kkk').read(sqls,(strs,'`id`',0,3))
    #print mysql
    #--------------------------------------------------------------------------------
    #第3种方式（推荐）
    # wherestr = "WHERE 1=1 "
    # wherestr = ' '.join((wherestr, "AND name like %s"))
    # wherestr = ' '.join((wherestr, "OR email like %s"))
    # wherestr = ' '.join((wherestr, 'AND status=%d' % (1,)))
    # wherestr = ' '.join((wherestr, 'Order by %s' % ('id desc')))
    # wherestr = ' '.join((wherestr, 'Limit %d,%d' % (0,2)))
    #
    # sqls = '''select * from user {where};'''.format(where=wherestr)
    # print(sqls)
    # strs='test'+"%"
    # mysql = Mysql('127.0.0.1', 'root', '123456', 'kkk').read(sqls,(strs,strs,))
    # print(mysql)
    #rd=Redis('10.10.100.21',6379,0,'')
    #print rd.add('b', '000')
    #print(rd.get('b'))
    #print rd.set('b','123')
    #print(rd.get('b'))
    #print rd.getset('b', '456')
    #print(rd.get('b'))
    #print rd.append('b', '7890')
    pass

